Introduction

This document helps new users create query-based custom monitors using PostgreSQL in OpsRamp. It guides users to implement PostgreSQL query-based metrics, configure monitors, and apply templates. It also outlines prerequisites, highlights limitations, provides troubleshooting steps, and answers frequently asked questions related to PostgreSQL database monitoring.

What Is PostgreSQL Database Monitoring

OpsRamp provides PostgreSQL Database Monitoring to help users track the health and performance of their PostgreSQL environments. It offers built-in performance metrics that allow users to visualize database availability and system health with ease. After logging into the OpsRamp portal, users can access real-time status updates and performance metric for specific PostgreSQL servers. For a complete list of available PostgreSQL metrics in OpsRamp, see PostgreSQL

How PostgresSQL Database Monitoring works

OpsRamp monitors PostgreSQL databases by executing SQL queries on target servers through a Gateway using JDBC connections. This process collects performance metrics and displays them in the OpsRamp interface.

Prerequisite

To enable monitoring, users must meet the prerequisites listed below.

• Ensure the database and port 5432 are reachable from the Gateway.
• Map a Gateway management profile to the target resource.
• Create credentials of type Database before assigning a template to the resource.The Port, Connection Timeout, and Is Secure values are not used when creating credentials.
• Enable privileges according to the PostgreSQL version used.

Privileges

PostgresDB

Prerequisites for applying PostgresDB templates:

• Use Gateway 7.0.0 and above.
• Create a Postgres environment file and provide the file path as the input parameter while applying the template. Along with setting up the Postgres environment, make sure that the environmental file includes other parameters, such as PGDATADIR, PGWALDIR, PGDATABASE, PGARCHIVEDIR, and PGPORT. Also, after loading the environment file, the pg_isready and psql commands are available for the script to use.
• Add the Postgres database credentials with the PostgresDB_Credentials set name.
• Assign the pg_monitor role to the user.

Template names:

• G2-PostgresDB-Performance

• G2-PostgresDB-Replication Running Status

  To use the multi-instance functionality with the G2-PostgresDB-Replication Running Status template, the credentials set name needs to be updated in the monitor script. Also, end-users need to create the credentials using the same credential set name on the device.

Discovery using the gateway

The gateway establishes a connection to the database using the Java Database Connectivity (JDBC) Java API and collects metrics using SQL queries. To monitor PostgreSQL, install gateway version 5.0.0 or later.

Use one of the following mechanisms to discover PostgreSQL servers and add them to your inventory:

• WMI
• SSH
• SNMP

Optionally, manually add a database server to the infrastructure inventory:

1. Select Infrastructure and click Resources.
2. Click the Settings icon on the top-right and click Add.
3. On the Add Device page, enter the Device Type and any other information you want to enter.
4. Click Save.

You can also apply agent-based templates to initiate MS SQL monitoring.

Set up PostgreSQL monitoring

PostgreSQL monitoring setup involves:

• Associating appropriate database credentials to the discovered database.
• Assigning one or more database-specific global monitors or templates on the device. Optionally, create custom metrics or monitors using SQL queries and assign them to the database.

PostgresSQL query based monitoring development

How to develop and create PostgresSQL metric

OpsRamp provides a predefined set of PostgreSQL metrics. For users who want to customize and build their own monitoring solutions, it also offers UI-based support for creating PostgreSQL metrics.

To create a PostgreSQL metric, users must ensure their query returns no more than one or two fields. Use the following use cases and examples to learn how to write queries for monitoring your PostgreSQL environment.

How to Prepare a PostgreSQL Metric Query

Metric Without Components
To create a metric query for a direct value without any components, return only one column in the query result. This single value will be treated as the metric value.

Examples:

• To monitor the count of WAL segments in PostgreSQL.

  Query
  

  select count(*) as "wal segment count" from pg_ls_dir('pg_wal') as t(fname) where fname <> 'archive_status';
  

   <img src="https://s3.dualstack.us-east-2.amazonaws.com/docsmedia.opsramp.com/2025-releases/content-releases/postgresql-solutions/postgresql-1.png" class="image-border" width= "800" height= "800" onclick="openModal('https://s3.dualstack.us-east-2.amazonaws.com/docsmedia.opsramp.com/2025-releases/content-releases/postgresql-solutions/postgresql-1.png')"></br>
     
   This metric query output is the count value of the wal segments, Here our framework should consider the value as metric value.</br>
  
  

• To monitor the activity count in PostgreSQL.

  Query
  

  SELECT count(*) as "activity_count" FROM pg_stat_activity;
  

   <img src="https://s3.dualstack.us-east-2.amazonaws.com/docsmedia.opsramp.com/2025-releases/content-releases/postgresql-solutions/postgresql-2.png" class="image-border" width= "600" height= "600" onclick="openModal('https://s3.dualstack.us-east-2.amazonaws.com/docsmedia.opsramp.com/2025-releases/content-releases/postgresql-solutions/postgresql-2.png')"></br>
     
   This metric query output is the count value of the activities, Here our framework should consider the value as metric value.</br>
  
  

Metric With Multiple Components
To create a metric query for component-based metrics, return two columns in the query result. The first column will be treated as the component name, and the second column will be treated as the metric value.

Examples:

• To monitor the number of transactions rolled back in each PostgreSQL database.

  select datname, xact_rollback from pg_stat_database;
  

  
   <img src="https://s3.dualstack.us-east-2.amazonaws.com/docsmedia.opsramp.com/2025-releases/content-releases/postgresql-solutions/postgresql-3.png" class="image-border" width= "600" height= "600" onclick="openModal('https://s3.dualstack.us-east-2.amazonaws.com/docsmedia.opsramp.com/2025-releases/content-releases/postgresql-solutions/postgresql-3.png')"></br>
  
  
   In this query:
   - `datname` identifies component name
   - `xact_rollback` provides metric value
  
  

• To monitor the size of the each PostgreSql Database.
  

  Query
  

  select datname as db, pg_database_size(datname) as size from pg_database;
  

   <img src="https://s3.dualstack.us-east-2.amazonaws.com/docsmedia.opsramp.com/2025-releases/content-releases/postgresql-solutions/postgresql-4.png" class="image-border" width= "600" height= "600" onclick="openModal('https://s3.dualstack.us-east-2.amazonaws.com/docsmedia.opsramp.com/2025-releases/content-releases/postgresql-solutions/postgresql-4.png')"></br>
     
   In this query:
   - `db` identifies the component name
   - `size` provides metric value
  
  

How to calculate/format metric values with example queries

To calculate metric values such as utilization, memory conversions (e.g., GB to MB), and time conversions (e.g., seconds to milliseconds), follow the examples below for better understanding.

Examples:

• To monitor utilization of the backend connections (relative to max_connections) in PostgreSQL.

  Query
  

  select datname as DataBase, (numbackends*100)/(select current_setting('max_connections')::int) as "Backend Connections %" from pg_stat_database WHERE datname IS NOT NULL
  

   <img src="https://s3.dualstack.us-east-2.amazonaws.com/docsmedia.opsramp.com/2025-releases/content-releases/postgresql-solutions/postgresql-6.png" class="image-border" width= "800" height= "800" onclick="openModal('https://s3.dualstack.us-east-2.amazonaws.com/docsmedia.opsramp.com/2025-releases/content-releases/postgresql-solutions/postgresql-6.png')"></br>
  
  

• To monitors the Time (in Hours) for long transactions in PostgreSQL.
  

  Query
  

  select application_name,extract(epoch from (now() - xact_start)/3600)::int as time from pg_stat_activity WHERE state IN ('idle in transaction', 'active') AND xact_start IS NOT NULL;
  

   <img src="https://s3.dualstack.us-east-2.amazonaws.com/docsmedia.opsramp.com/2025-releases/content-releases/postgresql-solutions/postgresql-7.png" class="image-border" width= "800" height= "800" onclick="openModal('https://s3.dualstack.us-east-2.amazonaws.com/docsmedia.opsramp.com/2025-releases/content-releases/postgresql-solutions/postgresql-7.png')"></br>
  
     
  

How to handle enum mappings & Alerting

Enumerated Map:

OpsRamp monitoring framework supports only integer-type metric values, so users must return metrics as integers—not as strings or other data types. If users prefer to work with string values, they can apply enum mappings to convert those strings into integers. The following are few examples providing further clarification.

Example:

To monitor the replication status of PostgreSql, it’s important to note that we have different possible states. In such cases, users must prepare a query to directly return the status value for the current replication status, as illustrated in the following:

Query

select client_addr, state from pg_stat_replication;

When creating a metric, users must select the Datapoint Value Conversion option as Enumerated Map and provide integer value mappings for each possible state.

Additionally, there is an option to enable Use formatted value in Alerts & Graphs. If users opt to enable this feature, the enumerated mapping values provided will affect Alerts & Graphs. Otherwise, these values will not be reflected anywhere. Please find below screenshots having enum mapping reflections in monitoring data.

If User provide string value mappings for each possible state (i.e. integer-type values to string-type values).

This configuration will be reflected in monitoring.

When a metric includes an enumerated map, follow these steps to define critical and warning alert conditions:

1. As outlined above, the prepared query returns the metric value as a string. To support alert configuration, users must define an enumerated map that assigns each possible string value to a unique integer.

2. To enable critical or warning alerts, users must identify all string-type values returned by the query and map them accordingly.

3. Refer to the following screenshot for guidance on configuring critical alerts when the enumerated map (string-to-integer conversion) is enabled.

4. Refer to the following screenshot for guidance on enabling critical alerts if the enumerated map (integer type to string type) is enabled.

How to create PostgresSQL Metric

Perform the following steps to create PostgreSQL metric.

1. Login to OpsRamp platform and Click on Setup from top menu and expand Monitoring from the side bar menu and click on Metrics.

2. Click + Add to create a new metric.

3. Configure the following steps on the Create Metric screen.

   1. Metric Scope: Select Service Provider Metric or Partner or Client Metric. Your access level and role may affect how this menu appears. If you select Partner or Client Metric, choose a Partner or Client from the dropdown. The list updates dynamically based on context.

   2. Adapter Type: Select Application from this dropdown.

   3. Application Type: Select Postgres SQL from this dropdown.

   
   

   4. Name: Provide a meaningful metric name.
   5. Tag Name: It is filled automatically with the same metric name.
   6. Display Name: Provide a meaningful metric display name.
   7. Description: Provide description about the metric.

   
   

   8. SQL Query: Provide the prepared PostgreSql query. (Note - No need to provide semicolon at the end of the query).

   
   

   9. Data Point type: Choose a suitable type from this dropdown. It has the following 8 pre-defined types. For this example, will go with “Gauge”

      • Counter Delta - It calculates delta on top of metric value.
        

          Counter Delta = (Current poll value - Prev poll value)
        

        Note: If the result is less than zero then it returns zero.

      • Counter Rate - It calculates rate on top of metric value.
        

          Counter Rate = (Current poll value - Prev poll value) / (Current poll time - Prev poll time)
        

        Note: If the result is less than zero then it returns zero.

      • Derive Delta - Not related to RSE. No support in both agent and gateway.

      • Derive Rate - Not related to RSE. No support in both agent and gateway.

      • Gauge - It returns direct metric value, which is returning from the script.

      • Rate - It calculates rate on top of metric value.
        

          Rate = (Current poll value - Prev poll value) / (Current poll time - Prev poll time)
        

        Note: If the result is less than zero then it returns negative value.

      • Delta - It calculates delta on top of metric value.
        

          Delta = (Current poll value - Prev poll value)
        

        Note: If the result is less than zero then it returns negative value.

      • None - Same as Gauge

   10. Units: Choose a suitable unit from the dropdown. For status-related metrics, units are not required, select None and proceed.

   
   

   11. Unit Multiplication Factor: Value to multiply the metric by. As this is status monitor, it doesn’t required this factor value. Select the default value 1.0.
       
   
   

   
   

   12. Datapoint value conversion: Choose a suitable option from any of the following.

       Value= Choose this option (As shown in below) when no conversion required on the metric value. This is the default value for Datapoint value conversion dropdown.

       
       

       
       

       Enumerated Map= Choose this option when enumeration based conversion is required.

       For status-related metrics, queries often return string-type values. However, monitoring graphs support only integer values. To ensure compatibility, users must define mappings that convert each possible string value into a corresponding integer.
       

       
       

       Sometimes If the query returns direct integer values to the metric but still the user wants to enable enumerated mapping then provide an enumerated map in reverse order. In this case the mapped strings displayed in the metric graph info page and alerts.

       Example:
       1- Running,
       0 - Stopped
       Based on user requirements, select the “Alerts” and “Graph” checkboxes accordingly. When both checkboxes are selected, Graphs as well as Alert subject and details page will display integer values alongside enumeration mappings. Additionally, these mappings will appear in the “Get Latest Metric Value” page against the metric.

   13. Metric Processing: Choose any of the following suitable option based on requirement.

       Option	Description
       Graph	Select if graphing is needed, but no alerting is required.
       Notification	Select if alerting is needed, but no graphing is required.
       Graph and Notification	Select if both graphing and alerting are needed.
       None	Select if neither graphing nor alerting is needed.

       
       

       
       

       For Notification, Graph and Notification, following options appears.

       
       

       Assign templates from setup

       Assign PostgreSQL templates to one or more resources for a selected client and change the configuration parameters while assigning templates. For more information, see Assign Templates from Setup.

       Assign templates at the device level

       Applying PostgreSQL templates at the device level helps assign one or more templates to a specific resource. You can change the configuration parameter default values while assigning the templates. For more information, see Assign Templates at the Device Level.

       Template configuration parameters:

       Configuration Parameter	Description
       Connection Time-out	The maximum time to connect to the database. The driver waits for the specified time before timing out due to connection failure. The default time is 6000 milliseconds (ms).
       Service Transport Type	To configure the database at a secure end-point. The default type is In-secure. The connection is Secure when the data sent between the client and server is encrypted.
       Service Port	The port number where the database is running. The connection is made to the specified port if you specify the port and the database name. The default port is 5432.
       DB Instance Name	The name of the database to connect to. The default name is root. If you have multiple instances with different ports, use the following syntax: Instance1:Port1,Instance2:Port2 Note: Do not consider the service port configuration value here. If you have a single instance, use the following syntax: “Instance1” Note: Consider the port from the service port configuration for this case. If you have multiple databases with the same port, use the following syntax: “Database1, Database2, Database3” Note: Consider the port from the service port configuration for this case.
       Application Type	The application type value to identify the adapter. For example, POSTGRESQL. Do not change the default application type value.

       Assign template from device management policies

       Device management policies help manage resources. You can assign monitoring templates, knowledge base articles, and custom attributes using device management policies. The device management policy can be applied to one or a set of resources. For more information, see Creating Policies.

       View resource metrics

       The gateway monitors the application using the assigned templates and displays the results in graphical format. To view resource metrics, click the database resource name > resource Details > Metrics.

       Troubleshooting

       If you have PostgreSQL monitoring issues, verify gateway, telnet, and database connectivity:

       ping <IP Address>
       telnet <IP Address> <Port>
       gcli
       db <Database Type> <IP Address> <User Name> <Password> <Port> <Database Name> <Connect Timeout> <ReadTimeout> <Secure Flag> <Query>

       Beginning with gateway version 5.3.0, use the following format for the last command, including the result-set:

       db <Database Type> <IP Address> <User Name> <Password> <Port> <Database Name> <Connect Timeout> <Read Timeout> <Secure Flag> <ResultSet?: Yes/No> <Query>

       Supported templates

       If multi-instance monitoring support is needed, you need to create monitor and template copies. This is a mandatory requirement for all multi-instance templates.

       Collector Type	Template Name
       Agent	Linux - PostgreSQL Monitors
       	PostgreSQL Status and Performance Check
       Gateway	Advanced Cloud Database Template for PostgreSQL
       	Advanced Cloud PostgreSQL Database BlockHits Template
       	Advanced Cloud PostgreSQL Database Connection Statistics Template
       	Advanced Cloud PostgreSQL Database Cpu Utilization Template
       	Advanced Cloud PostgreSQL Database Deadlocks Template
       	Advanced Cloud PostgreSQL Database Disk Usage Template
       	Advanced Cloud PostgreSQL Database IO Template
       	Advanced Cloud PostgreSQL Database Memory Template
       	Advanced Cloud PostgreSQL Database Performance Template
       	Advanced Cloud PostgreSQL Database Session Template
       	Advanced Cloud PostgreSQL Database Transactions RolledBack Template
       	G2 PostgreSQL Database Blockhits Template
       	G2 PostgreSQL Database Connection Statistics Template
       	G2 PostgreSQL Database CPU Utilization Template
       	G2 PostgreSQL Database Deadlocks Template
       	G2 PostgreSQL Database Disk Usage Template
       	G2 PostgreSQL Database IO Template
       	G2 PostgreSQL Database Locks Template
       	G2 PostgreSQL Database Memory Template
       	G2 PostgreSQL Database Performance Template
       	G2 PostgreSQL Database Session Template
       	G2 PostgreSQL Database Transactions Rollback Template
       Gateway v7 and above for MultiInstance	G2 PostgresDB Performance
       Gateway v7 and above for MultiInstance	G2-PostgresDB-Replication Running Status
       Gateway v10 and above for MultiInstance	G2 PostgreSQL Database BlockHits Template - MultiInstance
       	G2 PostgreSQL Database Connection Statistics Template - MultiInstance
       	G2 PostgreSQL Database Deadlocks Template - MultiInstance
       	G2 PostgreSQL Database Disk Usage Template - MultiInstance
       	G2 PostgreSQL Database IO Template - MultiInstance
       	G2 PostgreSQL Database Locks Template - MultiInstance
       	G2 PostgreSQL Database Memory Template - MultiInstance
       	G2 PostgreSQL Database Performance Template - MultiInstance
       	G2 PostgreSQL Database Session Template - MultiInstance
       	G2 PostgreSQL Database Transactions RolledBack Template - MultiInstance
       	G2 PostgreSQL Hot Standby Feature Status Template - MultiInstance
       	G2 PostgreSQL Activity Long Transactions Template - MultiInstance
       Gateway v10 and above for MultiInstance	G2-PostgresDB-PerformanceQueries
       Gateway v10 and above for MultiInstance	G2-PostgresDB-Replication

       Supported metrics

       Metric	Metric Display Name	Unit
       postgresql.activity.count The maximum number of connection limits and the clients displaying the database connections.	Postgresql Activity Count	-
       postgresql.activity.long_transactions Long-running transactions are bad because they prevent Postgres from vacuuming old data. This causes database bloat and, in extreme circumstances, shutdown due to transaction ID, xid, wraparound. Transactions should be kept as short as possible, ideally less than a minute.	Postgresql Activity Long Transactions	hours(h)
       postgresql.bgwriter.buffers_backend The number of buffers written directly by a backend.	Postgresql Bgwriter Buffers Backend	-
       postgresql.bgwriter.buffers_checkpoint The number of buffers written during checkpoints.	Postgresql Bgwriter Buffers Checkpoint	-
       postgresql.bgwriter.buffers_clean The number of buffers written by the background writer.	Postgresql Bgwriter Buffers Clean	-
       postgresql.bgwriter.checkpoints_req The number of requested checkpoints that are already executed.	Postgresql Bgwriter Checkpoints Request	-
       postgresql.bgwriter.checkpoints_timed The number of scheduled checkpoints that are already executed.	Postgresql Bgwriter Checkpoints Timed	-
       postgresql.class.relpages Display the tables and the respective indexes in the descending order of relpages.	Postgresql Class Relpages	count
       postgresql.database. deadlocks The number of deadlocks detected in each database.	Postgresql Database Deadlocks	count
       postgresql.database.blkshit The number of times disk blocks were found already in the buffer cache.	Postgresql Database Blocks Hits	-
       postgresql.database.conflict.deadlocks The number of conflicts in the database that hare cancelled due to deadlocks.	Postgresql Database Conflict Deadlocks	count
       postgresql.database.connections The number of active connections to postgres database.	Postgresql Database Connections	-
       postgresql.database.deadlocks The number of deadlocks detected in this database.	Postgresql Database Deadlocks	count
       postgresql.database.rows_deleted The number of rows deleted by queries in this database.	Postgresql Database Rows Deleted	-
       postgresql.database.rows_fetched The number of rows fetched by queries in this database.	Postgresql Database Rows Fetched	-
       postgresql.database.rows_inserted The number of rows inserted by queries in this database.	Postgresql Database Rows Inserted	-
       postgresql.database.rows_returned The number of rows returned by queries in this database.	Postgresql Database Rows Returned	-
       postgresql.database.rows_updated The number of rows updated by queries in this database.	Postgresql Database Rows Updated	-
       postgresql.database.size The size of the database.	Postgresql Database Size	gigabytes(GB)
       postgresql.database.temp_bytes Total amount of data written to temporary files by queries in this database. All temporary files are counted, regardless of why the temporary file was created, and regardless of the log_temp_files setting.	Postgresql Database Temporary Bytes	-